insert 操作时提示is full 问题原因

root@localhost 11:55:41 [t]>show table status from t like ‘tt2’ \G ; *************************** 1. row *************************** Name: tt2 Engine: MEMORY Version: 10 Row_format: Fixed Rows: 7056 Avg_row_length: 9440 Data_length: 67183232 Max_data_length: 67042880 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2023-02-13 11:47:46 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)

查看表信息，发现时memory 引擎表，大小刚好时64M。内存临时表大小超过max_heap_table_size时，就会报ERROR 1114 (HY000): The table ‘tt2’ is full

官方文档描述Memory 表大小受限于创建表时 max_heap_table_size

The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. To enforce different size limits for MEMORY tables, change the value of this variable. The value in effect for CREATE TABLE, or a subsequent ALTER TABLE or TRUNCATE TABLE, is the value used for the life of the table. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value. You can set the size for individual tables as described later in this section

e.g:

root@localhost 12:21:08 [t]>set max_heap_table_size=2097152 ;

root@localhost 12:25:39 [t]>create table q1 like information_schema.tables ; Query OK, 0 rows affected (0.02 sec)

root@localhost 12:25:46 [t]>insert into q1 select * from information_schema.tables ; ERROR 1114 (HY000): The table ‘q1’ is full

root@localhost 12:26:45 [t]>set max_heap_table_size=20971520 ;

root@localhost 12:27:12 [t]>create table q2 like information_schema.tables ; Query OK, 0 rows affected (0.02 sec)

root@localhost 12:27:18 [t]>insert into q2 select * from information_schema.tables ; Query OK, 287 rows affected (0.33 sec) Records: 287 Duplicates: 0 Warnings: 0

root@localhost 12:27:31 [t]>insert into q1 select * from information_schema.tables ; ERROR 1114 (HY000): The table ‘q1’ is full

表的max_heap_table_size 跟建表时环境有关系 解决方案

方案1：

root@localhost 12:30:38 [t]>insert into q1 select * from information_schema.tables ; ERROR 1114 (HY000): The table ‘q1’ is full

root@localhost 12:26:45 [t]>set max_heap_table_size=20971520 ;

root@localhost 12:32:30 [t]>alter table q1 engine = memory ; Query OK, 224 rows affected (0.03 sec) Records: 224 Duplicates: 0 Warnings: 0

root@localhost 12:32:38 [t]>insert into q1 select * from information_schema.tables ; Query OK, 287 rows affected (0.33 sec) Records: 287 Duplicates: 0 Warnings: 0

参考文档

The size of MEMORY tables is limited by the value of the max_heap_table_size system variable, which is not replicated (see Section 17.5.1.38, “Replication and Variables”). A change in max_heap_table_size takes effect for MEMORY tables that are created or updated using ALTER TABLE … ENGINE = MEMORY or TRUNCATE TABLE following the change, or for all MEMORY tables following a server restart. If you increase the value of this variable on the master without doing so on the slave, it becomes possible for a table on the master to grow larger than its counterpart on the slave, leading to inserts that succeed on the master but fail on the slave with Table is full errors. This is a known issue (Bug #48666). In such cases, you must set the global value of max_heap_table_size on the slave as well as on the master, then restart replication. It is also recommended that you restart both the master and slave MySQL servers, to insure that the new value takes complete (global) effect on each of them.